package com.lbd.statistics.service.impl;


import com.lbd.statistics.controller.vo.BorrowerStatisticsVo;
import com.lbd.statistics.service.BorrowerStatisticsService;
import org.apache.log4j.Logger;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.persistence.EntityManager;
import javax.persistence.Query;
import java.math.BigDecimal;
import java.util.HashMap;
import java.util.Map;


@Service
@Transactional
public class BorrowerStatisticsServiceImpl implements BorrowerStatisticsService {
    private final Logger log = Logger.getLogger(this.getClass());

    @Autowired
    public EntityManager em;

    @Override
    public BorrowerStatisticsVo findByTime(BorrowerStatisticsVo borrowerStatictisVo) {
        Map<String, Object> parameter = new HashMap<String, Object>();
        BorrowerStatisticsVo vo = new BorrowerStatisticsVo();
        HashMap<String, Object> map = new HashMap<String, Object>();
        StringBuffer sqlStr = new StringBuffer();
        if (borrowerStatictisVo != null) {
            if (borrowerStatictisVo.getStartDate() != null && borrowerStatictisVo.getStartDate().toString().length() > 0) {
                sqlStr.append(" and t.created_date >= :startDate");
                parameter.put("startDate", borrowerStatictisVo.getStartDate());

            }
            if (borrowerStatictisVo.getEndDate() != null && borrowerStatictisVo.getEndDate().toString().length() > 0) {
                sqlStr.append(" and t.created_date <= :endDate");
                parameter.put("endDate", borrowerStatictisVo.getEndDate());
            }
        }
        StringBuffer sql = new StringBuffer();
        /**
         * 查询总借款用户数、总借款笔数、总借款金额
         */
        try {
            sql.append("select COUNT(DISTINCT telephone) as borrower_num,COUNT(telephone) as borrow_num,sum(htje) as borrow_amount from target_info t");
            sql.append(" where 1=1 and t.target_state in (4,5)");
            sql.append(sqlStr);
            map = (HashMap<String, Object>) findMapByPageNativeQuery(parameter, sql.toString());
            /**
             * 总借款金额
             */
            BigDecimal borrowAmount = map == null || map.get("borrow_amount") == null ? BigDecimal.ZERO : BigDecimal.valueOf((double) map.get("borrow_amount"));
            vo.setBorrowAmount(borrowAmount);
            /**
             * 总借款笔数
             */
            Integer borrowNum = map == null || map.get("borrow_num") == null ? 0 : Integer.valueOf(map.get("borrow_num").toString());
            vo.setBorrowNum(borrowNum);
            /**
             * 总借款用户数
             */
            Integer borrowerNum = map == null || map.get("borrower_num") == null ? 0 : Integer.valueOf(map.get("borrower_num").toString());
            vo.setBorrowerNum(borrowerNum);
            /**
             * 人均累计借款金额
             */
            BigDecimal perCapitaBorrowAmount = borrowerNum == 0 ? BigDecimal.ZERO : borrowAmount.divide(BigDecimal.valueOf(borrowerNum.doubleValue())).setScale(2, BigDecimal.ROUND_HALF_EVEN);
            vo.setPerCapitaBorrowAmount(perCapitaBorrowAmount);
            /**
             * 每笔累计借款金额
             */
            BigDecimal eachBorrowAmount = borrowNum == 0 ? BigDecimal.ZERO : borrowAmount.divide(BigDecimal.valueOf(borrowNum.doubleValue())).setScale(2, BigDecimal.ROUND_HALF_EVEN);
            vo.setEachBorrowAmount(eachBorrowAmount);
        } catch (Exception e) {
            e.printStackTrace();
            log.error("查询总借款用户数、总借款笔数、总借款金额失败,e=" + e.getMessage());
        }

        /**
         * 查询累计待还本金、累计待还利息
         */
        try {
            sql = new StringBuffer();
            sql.append("select sum(t.due_repay_capital)  as tobepaid_principal ,sum(due_repay_interest) as tobepaid_interest,max(t.due_repay_capital+t.due_repay_interest) as most_tobepaid_amount from user_money_statistics t");
            sql.append(" where 1=1 ");
            sql.append(sqlStr);
            map = (HashMap<String, Object>) findMapByPageNativeQuery(parameter, sql.toString());
            /**
             * 累计待还本金
             */
            BigDecimal toBePaidPrincipal = map == null || map.get("tobepaid_principal") == null ? BigDecimal.ZERO : (BigDecimal) map.get("tobepaid_principal");
            vo.setToBePaidPrincipal(toBePaidPrincipal);
            /**
             * 累计待还利息
             */
            BigDecimal toBePaidInterest = map == null || map.get("tobepaid_interest") == null ? BigDecimal.ZERO : (BigDecimal) map.get("tobepaid_interest");
            vo.setToBePaidInterest(toBePaidInterest);
            /**
             * 累计待还总额
             */
            BigDecimal toBePaidAmount = toBePaidPrincipal.add(toBePaidInterest);
            vo.setToBePaidAmount(toBePaidAmount);
            /**
             * 最大单户融资余额占比
             */
            BigDecimal mostToBePaidRate = BigDecimal.ZERO;
            if (map != null && map.get("most_tobepaid_amount") != null && toBePaidAmount.compareTo(BigDecimal.ZERO) > 0) {
                BigDecimal mostToBePaidAmount = (BigDecimal) map.get("most_tobepaid_amount");
                mostToBePaidRate = mostToBePaidAmount.divide(toBePaidAmount, 2, BigDecimal.ROUND_HALF_EVEN).multiply(BigDecimal.valueOf(100));
            }
            vo.setMostToBePaidRate(mostToBePaidRate);

            /**
             * 最大十户融资余额占比
             */
            sql = new StringBuffer();
            sql.append("select sum(t.ss) as topten_tobepaid_amount from (select t.due_repay_capital+t.due_repay_interest as ss from user_money_statistics t ORDER BY ss desc LIMIT 10) t");
            sql.append(" where 1=1 ");
            sql.append(sqlStr);
            map = (HashMap<String, Object>) findMapByPageNativeQuery(parameter, sql.toString());
            BigDecimal topTenToBePaidRate = BigDecimal.ZERO;
            if (map != null && map.get("topten_tobepaid_amount") != null && toBePaidAmount.compareTo(BigDecimal.ZERO) > 0) {
                BigDecimal topTenToBePaidAmount = (BigDecimal) map.get("topten_tobepaid_amount");
                topTenToBePaidRate = topTenToBePaidAmount.divide(toBePaidAmount, 2, BigDecimal.ROUND_HALF_EVEN).multiply(BigDecimal.valueOf(100));
            }
            vo.setTopTenToBePaidRate(topTenToBePaidRate);

        } catch (Exception e) {
            e.printStackTrace();
            log.error("查询累计待还本金、累计待还利息、累计待还总额、最大单户融资余额占比、最大十户融资余额占比失败=" + e.getMessage());
        }

        /**
         * 累计逾期本金
         */
        BigDecimal overduePrincipal = map == null || map.get("overdue_principal") == null ? BigDecimal.ZERO : BigDecimal.valueOf((double) map.get("overdue_principal"));
        vo.setOverduePrincipal(overduePrincipal);
        /**
         * 累计逾期利息
         */
        BigDecimal overdueInterest = map == null || map.get("overdue_interest") == null ? BigDecimal.ZERO : BigDecimal.valueOf((double) map.get("overdue_interest"));
        vo.setOverdueInterest(overdueInterest);
        /**
         * 累计逾期总额
         */
        BigDecimal overdueAmount = map == null || map.get("overdue_amount") == null ? BigDecimal.ZERO : BigDecimal.valueOf((double) map.get("overdue_amount"));
        vo.setOverdueAmount(overdueAmount);
        /**
         * 项目逾期率（%）
         */
        BigDecimal targetOverdueRate = map == null || map.get("target_overdue_rate") == null ? BigDecimal.ZERO : BigDecimal.valueOf((double) map.get("target_overdue_rate"));
        vo.setTargetOverdueRate(targetOverdueRate);
        /**
         * 金额逾期率（%）
         */
        BigDecimal overdueRate = map == null || map.get("overdue_rate") == null ? BigDecimal.ZERO : BigDecimal.valueOf((double) map.get("overdue_rate"));
        vo.setOverdueRate(overdueRate);
        /**
         * 逾期项目个数
         */
        Integer overdueNum = map == null || map.get("overdue_num") == null ? 0 : Integer.valueOf(map.get("overdue_num").toString());
        vo.setOverdueNum(overdueNum);


        vo.setStartDate(borrowerStatictisVo.getStartDate());
        vo.setEndDate(borrowerStatictisVo.getEndDate());

        return vo;
    }

    /**
     * nativesql返回Map
     *
     * @param parameter nativeSql 的参数
     * @param nativeSql nativeSql 字符串
     * @return
     */
    public Map findMapByPageNativeQuery(Map<String, Object> parameter, String nativeSql) {
        Query nativeQuery = em.createNativeQuery(nativeSql);
        nativeQuery.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        for (String key : parameter.keySet()) {
            nativeQuery.setParameter(key, parameter.get(key));
        }
        return (Map) nativeQuery.getSingleResult();
    }
}
